﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class TableDB : DBAccess
    {
        /// <summary>
        /// return status of table
        /// </summary>
        /// <param name="idtables"></param>
        /// <returns>status table</returns>
        public int getStatus(int idtables)
        {
            string sql = "SELECT status FROM bill WHERE (status=1 OR status=2) AND tableid=" + idtables.ToString();
            SqlDataReader rd = ExecuteQuery(sql, CommandType.Text);
            if (rd.Read())
            {
                int statusTemp = int.Parse(rd["status"].ToString());
                rd.Close();
                return statusTemp;
            }
            rd.Close();

            return 3;// close
        }

        /// <summary>
        /// Close a table
        /// </summary>
        /// <param name="tablId">table need close</param>
        /// <returns></returns>
        public string CLoseTheTable(int tablId)
        {
            int status = getStatus(tablId);

            if (status == 1)
            {
                return "Table need is payed before Closing";
            }
            else
                if (status == 3)
                {
                    return "The table is closing";
                }
                else
                {
                    string sql = "UPDATE Bill SET status=3 WHERE tableId=" + tablId.ToString();
                    ExecuteNonQuery(sql, CommandType.Text);
                    return "";
                }
        }

        public IList<Table> getTableAvailable()
        {

            string sql = "select * from tables where visible=1 and  tableid not in(select tableid from bill where status=1 or status=2)";
            IList<Table> _listTable = new List<Table>();
            SqlDataReader rd = ExecuteQuery(sql, CommandType.Text);
            while (rd.Read())
            {
                Table _table = new Table();
                _table.Name = rd["name"].ToString();
                _table.TableId = int.Parse(rd["tableId"].ToString());
                _listTable.Add(_table);
            }
            rd.Close();
            return _listTable;
        }

        //return all table is busy
        public IList<Table> getTableBusy()
        {

            string sql = "SELECT * FROM tables WHERE visible=0 AND tableid  IN(SELECT tableid FROM bill WHERE status=1)";
            IList<Table> _listTable = new List<Table>();
            SqlDataReader rd = ExecuteQuery(sql, CommandType.Text);
            while (rd.Read())
            {
                Table _table = new Table();
                _table.Name = rd["name"].ToString();
                _table.TableId = int.Parse(rd["tableId"].ToString());
                _listTable.Add(_table);
            }
            rd.Close();
            return _listTable;
        }

        /// <summary>
        /// return table when has tableId
        /// </summary>
        /// <param name="tableId"></param>
        /// <returns>list table</returns>
        public IList<Table> getTableBusy(int tableId)
        {

            string sql = "SELECT * FROM tables WHERE visible=0 AND  tableid  IN(SELECT tableid FROM bill WHERE status=1 AND Tableid!=" + tableId + ")";
            IList<Table> _listTable = new List<Table>();
            SqlDataReader rd = ExecuteQuery(sql, CommandType.Text);
            while (rd.Read())
            {
                Table _table = new Table();
                _table.Name = rd["name"].ToString();
                _table.TableId = int.Parse(rd["tableId"].ToString());
                _listTable.Add(_table);
            }
            rd.Close();
            return _listTable;
        }

        /// <summary>
        /// get all tables has Visible = False
        /// </summary>
        /// <returns>Ilist table have visible =true</returns>
        public IList<Table> GetALl()
        {
            TableDB _tableDb = null;
            Table _item = null;

            this.Connect();

            IList<Table> _listTable = new List<Table>();
            SqlDataReader rd;
            _tableDb = new TableDB();

            //select all tables is Active
            rd = Returncommand("SELECT * FROM Tables WHERE Visible ='False'");

            while (rd.Read())
            {
                _item = new Table();
                _item.TableId = int.Parse(rd["TableId"].ToString());
                _item.Name = rd["Name"].ToString();
                _item.Status = _tableDb.getStatus(_item.TableId);                
                _item.Visible = bool.Parse(rd["Visible"].ToString());

                _listTable.Add(_item);
            }
            rd.Close();
            return _listTable;
        }

        public Table GetTableFromId(int id)
        {
            Table _table = null;
            string query = "SELECT * FROM tables WHERE tableid=" + id.ToString();
            SqlDataReader rd = ExecuteQuery(query, CommandType.Text);
            if (rd.Read())
            {
                _table = new Table();
                _table.TableId = id;
                _table.Name = rd["name"].ToString();
                _table.Visible = bool.Parse(rd["visible"].ToString());
                rd.Close();
            }
            return _table;
        }

        public Table GetTable(int id, IList<Table> _listTable)
        {
            foreach (Table _itemtable in _listTable)
            {
                if (_itemtable.TableId == id)
                    return _itemtable;
            }
            return null;
        }
    }
}
